package dblayer;

import modellayer.Customer;
import modellayer.Cities;
import java.sql.*;
import java.util.ArrayList;



/**
 * 
 * @author Gruppe 3
 *
 */
public class DBCustomer implements IFDBCustomer {

	 private Connection con;

	 /**
	  * 
	  */
     public DBCustomer() 
     {
    	 con = DBConnection.getInstance().getDBcon();
     }    
     
     /**
 	 * A arraylist with all Customer
 	 */
 	 public ArrayList<Customer> getAllCustomers(boolean retriveAssociation) {
 	 	return miscWhere("", retriveAssociation);
 	 }
 	 
 	public Customer searchCustomerOnName(String name, boolean retriveAssociation) {
 		String wClause = "name like % "+name+" %";
 		return singleWhere(wClause, retriveAssociation);
		 
 	}
 	 
 	 /**
      * Select a single Customer
      */
     public Customer selectSingleCustomer(int customerID, boolean retriveAssociation) throws SQLException {
    	 String wClause = "customerID = "+customerID;
         return singleWhere(wClause, retriveAssociation);
     }
     
     /**
      * Insert a Customer to the DB
      */
     public int insertCustomer(Customer c) throws SQLException 
     {
    	 PreparedStatement pstmt = null;
    	 int controlInt = -1;
    	 String insert = "insert into Customer(name, address, zipCode, phoneNo)"
                       + "values (?, ?, ?, ?)";
    	 //System.out.println(insert);
    	 try {
    		 pstmt = con.prepareStatement(insert);
    		 pstmt.setString(1, c.getName());
    		 pstmt.setString(2, c.getAddress());
             pstmt.setInt(3, c.getZipCode());
             pstmt.setString(4, c.getPhoneNo());
             controlInt = pstmt.executeUpdate();
         } catch (SQLException sqlE) {
             System.out.println("SQL Error, Customer not inserted");
             System.out.println(sqlE.getMessage());
         } catch (Exception e) {
             e.getMessage();
         }
    	 
    	 return controlInt;
     }
     
     /**
      * Updates the Customer db
      */
     @Override
     public int updateCustomer(Customer c) throws SQLException {
    	 
    	 int controlInt = -1;

    	 String update = "UPDATE Customer SET "
    	 		+ "name = ?, "
    	 		+ "address = ?, "
    	 		+ "phoneNo = ?, "
    	 		+ "zipCode = ? "
    	 		+ "WHERE customerID = ?";

    	 System.out.println(update);
    	 try {
    		 PreparedStatement pstmt = null;
    		 pstmt = con.prepareStatement(update);
    		 pstmt.setString(1, c.getName());
    		 pstmt.setString(2, c.getAddress());
             pstmt.setInt(3, c.getZipCode());
             pstmt.setString(4, c.getPhoneNo());
    		 pstmt.setInt(5, c.getZipCode());
    		 controlInt = pstmt.executeUpdate();
    	 } catch (SQLException sqlE) {
    		 System.out.println("SQL Error, Customer not updated");
    		 System.out.println(sqlE.getMessage());
    	 } catch (Exception e) {
    		 e.getMessage();
    	 }
    	 
    	 return controlInt;
     }
     
     /**
      * Delete a Customer from the database
      */
     public int deleteCustomer(Customer c) throws SQLException 
     {
    	 PreparedStatement pstmt = null;
    	 int controlInt = -1;
    	 
    	 String delete = "DELETE FROM Customer WHERE customerID = ?";
    	 //System.out.println(delete);
    	 try {
    		 pstmt = con.prepareStatement(delete);
             pstmt.setInt(1, c.getCustomerID());
             controlInt = pstmt.executeUpdate();
         } catch (SQLException sqlE) {
             System.out.println("SQL Error, Customer not deleted");
             System.out.println(sqlE.getMessage());
         } catch (Exception e) {
             e.getMessage();
         }
    	 
    	 return controlInt;
     }
     
     /**
 	 * If more than one Customer is to be selected
 	 * @param wClause
 	 * @return ArrayList with Customer objects
 	 */
     private ArrayList<Customer> miscWhere(String wClause, boolean retrieveAssociation)
 	 {
    	 ResultSet results;
 	     ArrayList<Customer> list = new ArrayList<Customer>();	
 		
 	     String query = buildQuery(wClause);
    
 	     try{ // read the cities from the database
 			 Statement stmt = con.createStatement();
 		 	 stmt.setQueryTimeout(5);
 		 	 results = stmt.executeQuery(query);
 		 	
 			 while( results.next() ){ // loop through all cities and create them as objects
 			     Customer cObj = new Customer();
 				 cObj = buildCustomer(results);	
 		         list.add(cObj);	
 			 }//end while
 			
 	         stmt.close();
 	         
 	        if(retrieveAssociation) {
 	        	for(Customer cObj : list) {
					IFDBCities dbCities = new DBCities();
					int zipCode = cObj.getZipCode();
					Cities cityObj = dbCities.selectSingleCity(zipCode);
					cObj.setCity(cityObj); //set the city object in the customer object
 	        	}//end for
			}//end if
 		 }//end try
 	 	 catch(Exception e){
 	 	 	System.out.println("Query exception - select: "+e);
 		 	e.printStackTrace();
 	 	 }
 		 return list;
 	 }
     
     /**
 	 * If only one city is to be selected
 	 * @param wClause
 	 * @return Customer object
 	 */
 	 private Customer singleWhere(String wClause, boolean retrieveAssociation)
 	 {
 	 	ResultSet results;
 	 	Customer cObj = new Customer();
 		        
 	 	String query = buildQuery(wClause);
 		//System.out.println(query);
 		
 		try{ 	
 			Statement stmt = con.createStatement();
 			stmt.setQueryTimeout(5);
 			results = stmt.executeQuery(query);
 			if( results.next() ){
 				cObj = buildCustomer(results);
 				stmt.close();
 				
 				if(retrieveAssociation) {
 					IFDBCities dbCities = new DBCities();
 					int zipCode = cObj.getZipCode();
 					Cities cityObj = dbCities.selectSingleCity(zipCode);
 					cObj.setCity(cityObj); //set the city object in the customer object
 				}
 				
 			}else{ //No customer found
 				cObj = null;
 			}
 		}//end try	
 		
 		catch(Exception e){
 			System.out.println("Query exception: "+e);
 		}
 		return cObj;
 	 }
 	 
 	 /**
 	 * Method to build the query
 	 * @param wClause
 	 * @return
 	 */
 	 private String buildQuery(String wClause)
 	 {
 	     String query="SELECT * FROM Customer";
 		
 	 	if (wClause.length()>0)
 	 		query=query+" WHERE "+ wClause;
 	 		
 	 	return query;
 	 }
     
     /**
      * Builds a Customer object from the data in the database
      * @param result
      * @return Customer object
      * @throws SQLException
      */
     private Customer buildCustomer(ResultSet result) throws SQLException {
    	 Customer cObj = new Customer();

    	 try {
    		 cObj.setName(result.getString("name"));
    		 cObj.setAddress(result.getString("address"));
    		 cObj.setZipCode(result.getInt("zipCode"));
    		 cObj.setPhoneNo(result.getString("phoneNo"));
    	 } catch (Exception e) {
    		 System.out.println("error building Customer object, " +e);
         }
         return cObj;
     }

}
